package sale;

import java.awt.Color;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.util.List;
import java.util.Vector;

import javax.swing.BorderFactory;
import javax.swing.Box;
import javax.swing.DefaultComboBoxModel;
import javax.swing.JButton;
import javax.swing.JComboBox;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JTextArea;
import javax.swing.JTextField;
import javax.swing.border.BevelBorder;


public class Bestseller extends JPanel{
    static  Connection conn = null;
    private static String username ="root";
    private static String password="root" ;
    private static String driverClassName="com.mysql.jdbc.Driver";
    private static String url="jdbc:mysql://127.0.0.1:3307/books_system";
    JLabel byear,bmonth,bday,breminder,btitle,btitlebig;
    JComboBox<String> cyear,cmonth,cday;
    JTextField tyear,tmonth,tday;
    JButton bbegin;
    Box box;
    JTextArea textArea;
    JPanel p1;
    int bnonum[][];
    private Vector rowData, columnName;
    private JTable jt = null;
    private JScrollPane jsp = null;
    public Bestseller(){
        btitlebig = new JLabel("查看畅销书单            ");
        btitlebig.setFont(new   java.awt.Font("楷体", 1, 20));
        columnName=new Vector();
        bnonum=new int[10][2];
        btitle=new JLabel("  ",JLabel.LEFT);
        btitle.setFont(new java.awt.Font("楷体", 1, 15));
        btitle.setBackground(new Color(100,100,100));//设置背景色
        p1=new JPanel();
        box=Box.createVerticalBox();
        //setLayout( new BorderLayout() );
        columnName.add("        图书编号               ");
        columnName.add("销售量");
        breminder=new JLabel(" 销售日期：     ");
        byear=new JLabel("     年         ");
        bmonth=new JLabel("     月         ");
        bday=new JLabel("     日         ");
        tyear=new JTextField(5);
        tmonth=new JTextField(5);
        tday=new JTextField(5);
        cyear = new JComboBox<String>();
        cmonth = new JComboBox<String>();
        cday = new JComboBox<String>();
        bbegin=new JButton("确定");
        cyear.setModel(new DefaultComboBoxModel<String>(new String[] {"","2017","2016","2015"}));
        cmonth.setModel(new DefaultComboBoxModel<String>(new String[] {"","01","02","03","04","05","06",
                "07","08","09","10","11","12"}));
        cday.setModel(new DefaultComboBoxModel<String>(new String[] {"","01","02","03","04","05","06",
                "07","08","09","10","11","12","13","14","15","16","17","18","19","20","21","22","23",
                "24","25","26","27","28","29","30"}));
        Box box1=Box.createHorizontalBox();
        box1.add(breminder);
        box1.add(cyear);
        box1.add(tyear);
        box1.add(byear);
        box1.add(cmonth);
        box1.add(tmonth);
        box1.add(bmonth);
        box1.add(cday);
        box1.add(tday);
        box1.add(bday);
        box1.add(bbegin);
        setBorder(BorderFactory.createBevelBorder(BevelBorder.RAISED));
        validate(); // validate layout
        box.add(Box.createVerticalStrut(15));
        box.add(btitlebig);
        box.add(Box.createVerticalStrut(15));
        box.add(box1);
        //p1.setSize(200, 200);
        setBorder(BorderFactory.createBevelBorder(BevelBorder.RAISED));
        //validate();
        box.add(Box.createVerticalStrut(30));
        box.add(btitle);
        box.add(Box.createVerticalStrut(15));
        box.add(p1);
        this.add(box);
        textArea = new JTextArea();
        cyear.addActionListener(
                new ActionListener() {

                    public void actionPerformed(ActionEvent e) {
                        // TODO Auto-generated method stub
                        tyear.setText((String) cyear.getSelectedItem());//设置文本框的内容
                    }//设置事件响应器

                });
        cmonth.addActionListener(
                new ActionListener() {

                    public void actionPerformed(ActionEvent e) {
                        // TODO Auto-generated method stub
                        tmonth.setText((String) cmonth.getSelectedItem());//设置文本框的内容
                    }//设置事件响应器

                });
        cday.addActionListener(
                new ActionListener() {

                    public void actionPerformed(ActionEvent e) {
                        // TODO Auto-generated method stub
                        tday.setText((String) cday.getSelectedItem());//设置文本框的内容
                    }//设置事件响应器

                });

        bbegin.addActionListener(
                new ActionListener() {
                    public void actionPerformed( ActionEvent event )
                    {
                        try {
                            DisplayRecord();
                        } catch (ParseException e) {
                            // TODO Auto-generated catch block
                            e.printStackTrace();
                        }
                    }
                });
    }

    public void DisplayRecord() throws ParseException{
        String syear=tyear.getText();
        String smonth=tmonth.getText();
        String sday=tday.getText();
        String sdate;
        rowData=new Vector();
        if(sday.equals("")){
            if(smonth.equals("")){
                sdate=syear;
            }
            else
                sdate=syear+'-'+smonth;
        }
        else
            sdate=syear+'-'+smonth+'-'+sday;

        try {
            ResultSet rs;
            conn = DriverManager.getConnection(url,username,password);
            Statement stmt=conn.createStatement();
            //多条数据时处理方式
            //select Bno,sum(Snum) as totalnum from sale where Sdate='2017-7-1' group by Bno order by totalnum desc
            rs = stmt.executeQuery("select Bno,sum(Snum) as totalnum from sale" +
                    " where Sdate like '"+sdate+"%"+"'"+
                    " group by Bno order by totalnum desc limit 0,10" );
            int i=0;
            while(rs.next()){
                Vector<String> line1 = new Vector<String>();
                line1.add(String.valueOf(rs.getInt("Bno")));
                line1.add(String.valueOf(rs.getInt("totalnum")));
                rowData.add(line1);
            }
            String outputdate;
            if(sday.equals("")){
                if(smonth.equals("")){
                    outputdate=syear+"年";
                }
                else
                    outputdate=syear+"年"+smonth+"月";
            }
            else
                outputdate=syear+"年"+smonth+"月"+sday+"日";
            btitle.setText(outputdate+"的畅销书单为:             ");
            p1.removeAll();
            jt = new JTable(rowData, columnName);
            jsp = new JScrollPane(jt);
            p1.add(jsp);
            p1.validate();
            p1.repaint();
            setSize( 300, 100 );  // set window size

        }
        catch ( SQLException sqlException ) {
            JOptionPane.showMessageDialog( null, sqlException.getMessage(),
                    "Database Error", JOptionPane.ERROR_MESSAGE );
        }

    }
}





